﻿-- ===============================================================================================
-- Basic View for Menus
-- ===============================================================================================

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[esf_sso].[VTreeBasicReference]'))
	DROP VIEW [esf_sso].[VTreeBasicReference]
GO

-- SELECT * FROM [esf_sso].[VTreeBasicReference]

CREATE VIEW [esf_sso].[VTreeBasicReference] AS
	
	WITH TreeBasRef (
		RelID, ID, ParentID, ParentTypeID, Level, TypeID
		,Code, ParentCode, Name, Description, Extra_I, Extra_II, Extra_III, IsEnabled 
		,CreationDateTime, LastUpdateDateTime)
	AS
	(
		-- First Level
		SELECT 
			TBR.ID
			,VBR.ID
			,NULL
			,NULL
			,0
			,VBR.BasicReferenceTypeID
			,VBR.Code
			,CONVERT (VARCHAR (100), NULL)
			,VBR.Name
			,VBR.Description
			,VBR.Extra_I
			,VBR.Extra_II
			,VBR.Extra_III
			,VBR.IsEnabled
			,VBR.CreationDateTime
			,VBR.LastUpdateDateTime
		FROM 
			esf_sso.TreeBasicReference TBR
		INNER JOIN
			esf_sso.VBasicReference VBR ON VBR.ID = TBR.ParentBasicReferenceID
		WHERE 
			TBR.BasicReferenceID IS NULL
		
		UNION ALL
		-- Recursive member definition
		SELECT 
			TBR.ID
			,VBR.ID
			,TBRF.ID
			,TBRF.TypeID
			,TBRF.Level + 1
			,VBR.BasicReferenceTypeID
			,VBR.Code
			,CONVERT (VARCHAR (100), TBRF.Code)
			,VBR.Name
			,VBR.Description
			,VBR.Extra_I
			,VBR.Extra_II
			,VBR.Extra_III
			,VBR.IsEnabled
			,VBR.CreationDateTime
			,VBR.LastUpdateDateTime
		FROM 
			esf_sso.TreeBasicReference TBR
		INNER JOIN
			esf_sso.VBasicReference VBR ON VBR.ID = TBR.BasicReferenceID
		INNER JOIN
			TreeBasRef TBRF ON TBRF.ID = TBR.ParentBasicReferenceID
	)

	SELECT
		*
	FROM 
		TreeBasRef
	
GO